﻿using Asxsyd92Core.Utils.Provider;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace WebOS.Bll
{
    public class DBConnectionBll
    {
        public static DBConnectionBll Instance
        {
            get { return SingletonProvider<DBConnectionBll>.Instance; }
        }
        public string GetTable_SqlServer()
        {
            return Asxsyd92Core.Utils.JSONhelper.ToJson(getTables_SqlServer());
        }
        /// <summary>
        /// 得到一个连接所有表
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public List<string> getTables_SqlServer()
        {

            using (SqlConnection sqlConn = new SqlConnection(Asxsyd92Core.Utils.SQLServer.SqlEasy.connString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (SqlException err)
                {

                    return new List<string>();
                }
                List<string> tables = new List<string>();
                string sql = "SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name";
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                {
                    SqlDataReader dr = sqlCmd.ExecuteReader();
                    while (dr.Read())
                    {
                        tables.Add(dr.GetString(0));
                    }
                    dr.Close();
                    return tables;
                }
            }
        }

        public string GetFields(string table)
        {
            using (SqlConnection sqlConn = new SqlConnection(Asxsyd92Core.Utils.SQLServer.SqlEasy.connString))
            {
                try
                {
                    sqlConn.Open();
                }
                catch (SqlException err)
                {
                    return Asxsyd92Core.Utils.JSONhelper.ToJson(null); //new Dictionary<string, string>();
                }
                List<string> fields = new List<string>();
                string sql = string.Format(@"SELECT a.name as f_name, b.value from 
        sys.syscolumns a LEFT JOIN sys.extended_properties b on a.id=b.major_id AND a.colid=b.minor_id AND b.name='MS_Description' 
            WHERE object_id('{0}')=a.id ORDER BY a.colid", table);
                using (SqlCommand sqlCmd = new SqlCommand(sql, sqlConn))
                {
                    SqlDataReader dr = sqlCmd.ExecuteReader();
                    while (dr.Read())
                    {
                        fields.Add(dr.GetString(0));
                        //fields.Add(dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetString(1));
                    }
                    dr.Close();
                    return Asxsyd92Core.Utils.JSONhelper.ToJson(fields);

                }
            }
        }
    }
}
